package org.geekbang.dbexcel.Service.excelService.impl;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.geekbang.dbexcel.Service.excelService.ExcelService;
import org.geekbang.dbexcel.domain.Column;
import org.geekbang.dbexcel.domain.Table;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.List;

/**
 * @author 孙红瑞
 * @ClassName ExcelServiceImpl.java
 * @Description TODO
 * @createTime 2021年02月03日 18:52:00
 */
public class ExcelServiceImpl implements ExcelService {

    /**
     * 创建excel
     * @param file  文件路径
     * @param tableVo 表信息
     */
    public static void createExcel(String file , Table tableVo){
        FileOutputStream out = null;
        FileInputStream fis = null;
        try{
            fis = new FileInputStream(file);
            //创建工作薄对象
            XSSFWorkbook workbook = new XSSFWorkbook(fis);
            //复制下标为2的sheet,创建工作表对象
            XSSFSheet sheet = workbook.cloneSheet(2,tableVo.getTableComment());
            //创建工作表的行
            List<Column> columns = tableVo.getColumns();
            int size = columns .size();
            //当前行索引
            int rowIndex = 3;
            //编译每一行
            for (int j = 0;j< size ;j++){
                Column column = columns.get(j);
                //创建Rows 从第四行开始
                XSSFRow row = sheet.createRow(rowIndex++);
                row.createCell(0).setCellValue(j+1);
                //表名
                if(j == 0){
                    row.createCell(1).setCellValue(tableVo.getTableComment());
                    //表物理名
                    row.createCell(2).setCellValue(StringUtils.upperCase(tableVo.getTableName()));
                    //表备注说明
                    row.createCell(3).setCellValue(tableVo.getTableComment());
                }
                //列值
                row.createCell(4).setCellValue(j+1);
                //列名
                row.createCell(5).setCellValue(column.getColumnComment());
                //列物理名
                row.createCell(6).setCellValue(StringUtils.upperCase(column.getColumnName()));
                //列备注说明
                row.createCell(7).setCellValue(column.getColumnComment());
                //列属性
                row.createCell(8).setCellValue(column.getColumnType());
                //是否为空
                row.createCell(9).setCellValue(column.getIsNullable());
                //类型
                row.createCell(10).setCellValue("TABLE");
                // 主键名称
                row.createCell(11).setCellValue(column.getColumnKey());

                //设置样式
                XSSFCellStyle style = workbook.createCellStyle();
                style.setBorderRight(BorderStyle.THICK);
                XSSFCell cell13 = row.createCell(13);
                XSSFCell cell14 = row.createCell(14);
                cell13.setCellStyle(style);
                cell14.setCellStyle(style);

            }
            XSSFRow row4 = sheet.getRow(size+5);
            XSSFCell xssfCell4 = row4.getCell(0);
            xssfCell4.setCellValue("建表SQL脚本");

            XSSFFont font1 = workbook.createFont();
            font1.setBold(true);
            XSSFCellStyle style1 = workbook.createCellStyle();
            style1.setFont(font1);
            xssfCell4.setCellStyle(style1);

            XSSFRow row3 = sheet.getRow(size+6);
            XSSFCell xssfCell = row3.getCell(0);
            xssfCell.setCellValue(tableVo.getCreateSql());

            //  合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
            //  行和列都是从0开始计数，且起始结束都会合并
            //  这里是合并excel中日期的两行为一行
            CellRangeAddress region = new CellRangeAddress(size+6, size+40, 0, 3);
            sheet.addMergedRegion(region);
            // 下边框
            RegionUtil.setBorderBottom(BorderStyle.THICK, region, sheet);
            // 左边框
            RegionUtil.setBorderLeft(BorderStyle.THICK, region, sheet);
            // 有边框
            RegionUtil.setBorderRight(BorderStyle.THICK, region, sheet);
            // 上边框
            RegionUtil.setBorderTop(BorderStyle.THICK, region, sheet);
            try(ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
                workbook.write(outputStream);
                //输出目录
                try(FileOutputStream fileOutputStream = new FileOutputStream(file);
                    ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(outputStream.toByteArray())) {
                    byte[] bytes = new byte[1024];
                    int len;
                    while ((len = byteArrayInputStream.read(bytes)) != -1){
                        fileOutputStream.write(bytes,0,len);
                    }
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally{
            try{
                if(fis != null){
                    fis.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
            try{
                if(out != null){
                    out.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }
}
